﻿Imports VN.INS.DL.DH
Imports VN.INS.BL.BE

Public Class MantenimientoPeriodoDA

    Public Function ListaPeriodo() As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "select P.PERIODO AS PERIODO,P.DESCRIPCION,P.ANHIO,G.MES, P.MES AS COD_MES,P.TIPO_PERIODO,G.ESTADO as ESTADO,P.ESTADO COD_ESTADO from periodo p " & _
                      "left join  (select CODIGO,DESCRIPCION AS ESTADO from general WHERE ID='ESTADO_PERIODO' )  G ON G.CODIGO=P.ESTADO " & _
                      " left join (select CODIGO,DESCRIPCION AS MES  from general WHERE ID='MES' )  G ON G.CODIGO=P.MES   ORDER BY TIPO_PERIODO  ASC,PERIODO DESC,COD_MES DESC,ANHIO ASC"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaTPeriodo() As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT  distinct  ( tipo_periodo ) CODIGO,  ( tipo_periodo ) TIPO_PERIODO from periodo "
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaMes() As DataTable
        Dim objLM As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "select CODIGO,DESCRIPCION from(general) where ID like 'MES' "
            objDt = objLM.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaAño() As DataTable
        Dim objLA As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            'lxc_SQL = "SELECT DISTINCT ( ANHIO )  CODIGO,  ( ANHIO ) ANHIO  FROM Periodo "
            lxc_SQL = "SELECT DISTINCT ( CODIGO )  CODIGO,  ( DESCRIPCION ) ANHIO  FROM GENERAL WHERE ID='ANHIO' "

            objDt = objLA.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Sub insertarMPeriodo(ByVal PRDO As String, ByVal DESC As String, ByVal ANHIO As String, ByVal MES As String,
                                ByVal TP As String, ByVal EST As String)
        Dim objData As AdministradorOracle = New AdministradorOracle
        Try
            objData.CreateInputOracle("PRDO", PRDO, 10)
            objData.CreateInputOracle("DES", DESC, 50)
            objData.CreateInputOracle("ANHIO", ANHIO, 4)
            objData.CreateInputOracle("MES", MES, 2)
            objData.CreateInputOracle("TP", TP, 50)
            objData.CreateInputOracle("EST", EST, 2)

            objData.ExecuteOracle_NonQuery("INSERTARMANTPERIODO", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    Public Sub editarMPeriodo(ByVal PRDO As String, ByVal DESC As String, ByVal ANHIO As String, ByVal MES As String,
                                ByVal TP As String, ByVal EST As String)
        Dim objData As AdministradorOracle = New AdministradorOracle
        Try
            objData.CreateInputOracle("PRDO", PRDO, 10)
            objData.CreateInputOracle("DES", DESC, 50)
            objData.CreateInputOracle("ANHIOX", ANHIO, 4)
            objData.CreateInputOracle("MESX", MES, 2)
            objData.CreateInputOracle("TP", TP, 50)
            objData.CreateInputOracle("EST", EST, 2)

            objData.ExecuteOracle_NonQuery("EDITAMANTPERIODO", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Function contarperiodo(ByVal PERIODO As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objdt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT SUM(NUMERO) AS NUMERO FROM(SELECT COUNT(PTP.PERIODO) NUMERO FROM PLAN_TRABAJO_PERSONA PTP WHERE PTP.PERIODO= '" & PERIODO & "' " & _
                      "UNION " & _
                      "SELECT COUNT(SPF.PERIODO) NUMERO FROM SEG_PERIODO_AVAN_FINAN SPF WHERE '" & PERIODO & "' IN (SPF.PERIODO))"
            objdt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objdt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Sub eliminarperiodo(ByVal PRDO As String)
        Dim objData As AdministradorOracle = New AdministradorOracle
        Try
            objData.CreateInputOracle("PRDO", PRDO, 8)

            objData.ExecuteOracle_NonQuery("eliminarperiodo", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

End Class
